package esya;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Query {
	
	String tableName="EVENTS";

	String[] inpSplit= new String[2];
	
	
	public String nQuery(String s,String c) throws ClassNotFoundException, SQLException{
		
		boolean flag=false; 		//to check if the input is present in the DB.
		
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost/mysql?" + "user=root&password=";
	    Connection conn = DriverManager.getConnection(url);
		
		Statement stmt = null;
        ResultSet rs = null;
        
        String createString;
        
        createString="USE se";
        
        stmt = conn.createStatement();
        stmt.executeQuery(createString);
        
        //rs=stmt.executeQuery(createString);
        
		createString= "SELECT * FROM "+ tableName +" WHERE "+c+"= \""+s+" \" ";
		
		stmt = conn.createStatement();
        stmt.executeQuery(createString);
        
        rs=stmt.executeQuery(createString);
        
        StringBuffer sb=new StringBuffer();
        String s1;
        
        String[] st={"EVENT_NAME","DATE","TIME","VENUE","DETAILS"};
       
        
        while(rs.next()){
        	
        	int i=0;
        	while(i<5){
        		s1=rs.getString(st[i]);
        		sb.append(" "+st[i]+": "+s1);
        		i++;
        		
        	}
        	flag=true;
        }
 
     String result;
     
     
     
     if(!flag) result="Invalid input. Please check and try again.";
     
     else{
    	 result=sb.toString();
     }
        stmt.close();
        conn.close();
    
	return result;
	}
	
	
	public String nameQuery(String st) throws ClassNotFoundException, SQLException{
		
		Query qu=new Query();
		String r=qu.nQuery(st, "event_name");
		
		if(r.compareToIgnoreCase("Invalid input. Please check and try again.")==0){
			
			String i=qu.nQuery(st,"keyOne");
			
			if(i.compareToIgnoreCase("Invalid input. Please check and try again.")==0){
				
				String n=qu.nQuery(st,"keyTwo");
				
				if(n.compareToIgnoreCase("Invalid input. Please check and try again.")==0){
					String g=qu.nQuery(st,"keyThree");
					return g;
					
				}
				else return n;
				
			}
			else return i;
		}
		else return r;
		
		
		
	}
	
	
	
	public String dateQuery(String d) throws SQLException, ClassNotFoundException{
		
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost/mysql?" + "user=root&password=";
	    Connection conn = DriverManager.getConnection(url);
		
		Statement stmt = null;
        ResultSet rs = null;
        
        boolean flag=false;
        
        String createString;
        
        createString="USE se";
        
        stmt = conn.createStatement();
        stmt.executeQuery(createString);
        
        //rs=stmt.executeQuery(createString);
		createString= "SELECT * FROM "+ tableName +" WHERE date like '%"+d+"%'";
		
		stmt = conn.createStatement();
        stmt.executeQuery(createString);
        
        rs=stmt.executeQuery(createString);
        
        StringBuffer sb2=new StringBuffer();
        String s2;
        
        String[] st={"EVENT_NAME","DATE","TIME","VENUE","DETAILS"};

        while(rs.next()){
        	int i=0;
        	while(i<5){
        		s2=rs.getString(st[i]);
        		sb2.append(" "+st[i]+": "+s2);
        		i++;
        	}
        	flag=true;
        }
        String result;
        
        if(!flag) result="Sorry, no event on this date";
        
        else{
       	 result=sb2.toString();
        }
           stmt.close();
           conn.close();
       
   	return result;
	}
	
	
	
public String venueQuery(String v) throws SQLException, ClassNotFoundException{
		
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost/mysql?" + "user=root&password=";
	    Connection conn = DriverManager.getConnection(url);
		
		Statement stmt = null;
        ResultSet rs = null;
        
        boolean flag=false;
        
        String createString;
        
        createString="USE se";
        
        stmt = conn.createStatement();
        stmt.executeQuery(createString);
        
        //rs=stmt.executeQuery(createString);
		createString= "SELECT * FROM "+ tableName +" WHERE venue like '%"+v+"%'";
		
		stmt = conn.createStatement();
        stmt.executeQuery(createString);
        
        rs=stmt.executeQuery(createString);
        
        StringBuffer sb2=new StringBuffer();
        String s2;
        
        String[] st={"EVENT_NAME","DATE","TIME","VENUE","DETAILS"};
        
        while(rs.next()){
        	int i=0;
        	while(i<5){
        		s2=rs.getString(st[i]);
        		sb2.append(" "+st[i]+": "+s2);
        		i++;
        	}
        	flag=true;
        }
        String result;
        
        if(!flag) result="Sorry, no event at this venue";
        
        else{
       	 result=sb2.toString();
        }
           stmt.close();
           conn.close();
       
   	return result;
	}



public String timeQuery(String t) throws SQLException, ClassNotFoundException{
	
	Class.forName("com.mysql.jdbc.Driver");
	String url = "jdbc:mysql://localhost/mysql?" + "user=root&password=";
    Connection conn = DriverManager.getConnection(url);
	
	Statement stmt = null;
    ResultSet rs = null;
    
    boolean flag=false;
    
    String createString;
    createString="USE se";
    
    stmt = conn.createStatement();
    stmt.executeQuery(createString);
    
    //rs=stmt.executeQuery(createString);
	createString= "SELECT * FROM "+ tableName +" WHERE time =\""+t+" \" ";
	
	stmt = conn.createStatement();
    stmt.executeQuery(createString);
    
    rs=stmt.executeQuery(createString);
    
    StringBuffer sb2=new StringBuffer();
    String s2,s3;
    
    compDate cd= new compDate();
    String dat=cd.comp();
    System.out.println(dat);
    
    String[] st={"EVENT_NAME","DATE","TIME","VENUE","DETAILS"};
    
    while(rs.next()){
    	
    	s3=rs.getString(st[1]);
    	//System.out.println(s3);
    	if(s3.contains(dat)){
    	
    	int i=0;
    	while(i<5){
    		s2=rs.getString(st[i]);
    		sb2.append(" "+st[i]+": "+s2);
    		i++;
    	}
    	flag=true;
    }
    }
    
    String result;
    
    if(!flag) result="Sorry, no event at this time today";
    
    else{
   	 result=sb2.toString();
    }
       stmt.close();
       conn.close();
   
	return result;
}

}
